﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;
using System.Xml;
using System.Collections;

namespace ConnectServer
{
    public static class DirectConnect
    {
        public static int CheckNow = 1;
        public static int p = 0;
        public static string Source = "";
        public static string Conn = "Data Source=122.155.190.7; uid=jigsaw; pwd=TEERAman659; Initial Catalog=DB_Jigsaw;";
        //public static string Conn = @"Data Source=192.168.1.3; uid=sa; pwd=nextwaver; Initial Catalog=DB_Jigsaw;";
        public static DataSet Retreive(string sql, string Connection)
        {
            #region Code
            try
            {
                if (DirectConnect.CheckNow == 1)
                {
                    p = 0;
                    SqlConnection conn = new SqlConnection(Connection);
                    if (conn.State == System.Data.ConnectionState.Open) conn.Close();
                    conn.Open();
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = sql;
                    cmd.CommandTimeout = 3600;
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = cmd;
                    DataSet ds = new DataSet();
                    p = 1;
                    da.Fill(ds, "DATA");
                    conn.Close();
                    conn.Dispose();
                    return ds;
                }
                else if (DirectConnect.CheckNow == 2)
                {
                    p = 0;
                    Get_SourceForServer2();
                    DirectConnect.CheckNow = 2;
                    SqlConnection conn = new SqlConnection(Source);

                    if (conn.State == System.Data.ConnectionState.Open) conn.Close();
                    conn.Open();
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = sql;
                    cmd.CommandTimeout = 3600;
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = cmd;
                    DataSet ds = new DataSet();
                    p = 1;
                    da.Fill(ds, "DATA");
                    conn.Close();
                    conn.Dispose();
                    return ds;
                }
                else
                {
                    Get_SourceForServer3();
                    DirectConnect.CheckNow = 3;
                    SqlConnection conn = new SqlConnection(Source);

                    if (conn.State == System.Data.ConnectionState.Open) conn.Close();
                    conn.Open();
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = sql;
                    cmd.CommandTimeout = 3600;
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = cmd;
                    DataSet ds = new DataSet();
                    da.Fill(ds, "DATA");
                    conn.Close();
                    conn.Dispose();
                    return ds;
                }
            }
            catch
            {
                if (p == 0)
                {
                    p = p + 1;
                    try
                    {
                        Get_SourceForServer2();
                        DirectConnect.CheckNow = 2;
                        SqlConnection conn = new SqlConnection(Source);
                        if (conn.State == System.Data.ConnectionState.Open) conn.Close();
                        conn.Open();
                        SqlCommand cmd = conn.CreateCommand();
                        cmd.CommandText = sql;
                        cmd.CommandTimeout = 3600;
                        SqlDataAdapter da = new SqlDataAdapter();
                        da.SelectCommand = cmd;
                        DataSet ds = new DataSet();
                        da.Fill(ds, "DATA");
                        conn.Close();
                        conn.Dispose();
                        UpServerFailed();
                        //MessageBox.Show(@"MODE OFFLINE Server2 192.168.1.254\SQL2008R2 !! สาขาหนึ่ง ");
                        return ds;
                    }
                    catch
                    {
                        try
                        {
                            UpServerFailed();
                            Get_SourceForServer3();
                            DirectConnect.CheckNow = 3;
                            //string Connn = @"Data Source=localhost; uid=sa; pwd=nextwaver; Initial Catalog=DB_Jigsaw;";
                            SqlConnection conn = new SqlConnection(Source);
                            if (conn.State == System.Data.ConnectionState.Open) conn.Close();
                            conn.Open();
                            SqlCommand cmd = conn.CreateCommand();
                            cmd.CommandText = sql;
                            cmd.CommandTimeout = 3600;
                            cmd.CommandType = CommandType.Text;
                            SqlDataAdapter da = new SqlDataAdapter();
                            da.SelectCommand = cmd;
                            DataSet ds = new DataSet();
                            da.Fill(ds, "DATA");
                            conn.Close();
                            conn.Dispose();
                            UpServerFailed();
                            //MessageBox.Show("MODE OFFLINE Server3 192.168.1.250 !! สาขาสอง ");
                            return ds;
                        }
                        catch
                        {
                            //UpServerFailed();
                            //MessageBox.Show("SERVER NOT CONNECT IN MODE OFFLINE.");
                            return null;
                        }
                    }

                }
                return null;
            }
            #endregion
        }

        public static bool Execute(string[] sql, string Connection)
        {
            #region Code
            SqlTransaction transaction = null;
            string sql_temp = "";
            int Index = 0;
            try
            {
                SqlConnection conn = new SqlConnection(Connection);
                if (conn.State == System.Data.ConnectionState.Open) conn.Close();
                conn.Open();

                transaction = conn.BeginTransaction();
                for (Index = 0; Index < sql.Length; Index++)
                {
                    sql_temp = sql[Index];
                    new SqlCommand(sql[Index], conn, transaction).ExecuteNonQuery();
                }
                transaction.Commit();
                return true;
            }
            catch (Exception ex)
            {
                ErrorLog = " เกิดข้อผิดพลาด=>" + ex.Message + Environment.NewLine + "คำสั่งที่ " + sql_temp;
                if (transaction != null) transaction.Rollback();
                return false;
            }
            #endregion
        }
        public static bool Execute(string sql, string Connection)
        {
            #region Code

            try
            {
                if (DirectConnect.CheckNow == 1)
                {
                    p = 0;
                    SqlConnection conn = new SqlConnection(Connection);
                    if (conn.State == System.Data.ConnectionState.Open) conn.Close();
                    conn.Open();
                    SqlCommand comm = new SqlCommand();
                    comm.CommandText = sql;
                    comm.CommandType = System.Data.CommandType.Text;
                    comm.Connection = conn;
                    comm.ExecuteNonQuery();
                    conn.Close();
                    p = 1;
                }
                else if (DirectConnect.CheckNow == 2)
                {
                    p = 0;
                    Get_SourceForServer2();
                    DirectConnect.CheckNow = 2;

                    //string Connn = @"Data Source=localhost; uid=sa; pwd=nextwaver; Initial Catalog=DB_Jigsaw;";
                    SqlConnection conn = new SqlConnection(Source);
                    if (conn.State == System.Data.ConnectionState.Open) conn.Close();
                    conn.Open();
                    SqlCommand comm = new SqlCommand();
                    comm.CommandText = sql;
                    comm.CommandType = System.Data.CommandType.Text;
                    comm.Connection = conn;
                    comm.ExecuteNonQuery();
                    conn.Close();
                    SQLLog.Writer.Write(sql);
                    p = 1;
                }
                else
                {
                    Get_SourceForServer3();
                    DirectConnect.CheckNow = 3;

                    //string Connn = @"Data Source=localhost; uid=sa; pwd=nextwaver; Initial Catalog=DB_Jigsaw;";
                    SqlConnection conn = new SqlConnection(Source);
                    if (conn.State == System.Data.ConnectionState.Open) conn.Close();
                    conn.Open();
                    SqlCommand comm = new SqlCommand();
                    comm.CommandText = sql;
                    comm.CommandType = System.Data.CommandType.Text;
                    comm.Connection = conn;
                    comm.ExecuteNonQuery();
                    SQLLog.Writer.Write(sql);
                    conn.Close();
                }
                return true;
            }
            catch
            {
                if (p == 0)
                {
                    p++;
                    try
                    {
                        Get_SourceForServer2();
                        DirectConnect.CheckNow = 2;

                        //string Connn = @"Data Source=localhost; uid=sa; pwd=nextwaver; Initial Catalog=DB_Jigsaw;";
                        SqlConnection conn = new SqlConnection(Source);
                        if (conn.State == System.Data.ConnectionState.Open) conn.Close();
                        conn.Open();
                        SqlCommand comm = new SqlCommand();
                        comm.CommandText = sql;
                        comm.CommandType = System.Data.CommandType.Text;
                        comm.Connection = conn;
                        comm.ExecuteNonQuery();
                        conn.Close();
                        SQLLog.Writer.Write(sql);
                        //MessageBox.Show(@"MODE OFFLINE Server2 192.168.1.254\SQL2008R2 !! สาขาหนึ่ง ");
                        return true;
                    }
                    catch
                    {
                        try
                        {
                            Get_SourceForServer3();
                            DirectConnect.CheckNow = 3;

                            //string Connn = @"Data Source=localhost; uid=sa; pwd=nextwaver; Initial Catalog=DB_Jigsaw;";
                            SqlConnection conn = new SqlConnection(Source);
                            if (conn.State == System.Data.ConnectionState.Open) conn.Close();
                            conn.Open();
                            SqlCommand comm = new SqlCommand();
                            comm.CommandText = sql;
                            comm.CommandType = System.Data.CommandType.Text;
                            comm.Connection = conn;
                            comm.ExecuteNonQuery();
                            conn.Close();
                            SQLLog.Writer.Write(sql);
                            //MessageBox.Show("MODE OFFLINE Server3 192.168.1.250 !! สาขาสอง ");
                            return true;
                        }
                        catch
                        {
                            //MessageBox.Show("MODE OFFLINE AS SERVER NOT CONNECT.");
                            return false;
                        }
                    }
                    return true;
                }
                return true;
            }
            #endregion
        }
        public static bool IsServerConnected(string connectionString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    return true;
                }
                catch (SqlException)
                {
                    return false;
                }
            }
        }
        private static string LastQuery = "";
        static string ErrorLog;
        /// <summary>
        /// Error Log
        /// </summary>
        public static string _ErrorLog
        {
            set { ErrorLog = value; }
            get { return ErrorLog; }
        }
        public static bool Execute(string SQL, string Parameter, string img, string Connection)
        {
            if (img != null)
            {
                try
                {
                    SqlConnection conn = new SqlConnection(Connection);
                    if (conn.State == System.Data.ConnectionState.Open) conn.Close();
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(SQL, conn);
                    SqlParameter pic = new SqlParameter(Parameter, SqlDbType.Text);
                    pic.Value = img;
                    cmd.Parameters.Add(pic);
                    cmd.Connection = conn;
                    cmd.CommandTimeout = 250;
                    cmd.ExecuteNonQuery();
                    conn.Close();
                    return true;
                }
                catch (Exception ex)
                {
                    ErrorLog = ex.Message;
                    return false;
                }
            }
            else
            {
                ErrorLog = "ไม่พบข้อมูล Byte[] ที่ส่งมา";
                return false;
            }
        }

        public static void Get_SourceForServer2()
        {
            Source = "Data Source=@ServerName; uid=@UserName; pwd=@Password; Initial Catalog=@Catalog;";
            string part = Application.StartupPath + "/ServerConfig.xml";
            XmlDocument Xml = new XmlDocument();
            Xml.Load(part);
            ArrayList Arr = new ArrayList();
            string selectNode = @"//Document/Data/server2/Items";
            XmlNode nodeSection = Xml.SelectSingleNode(selectNode);
            for (int i = 0; i < nodeSection.ChildNodes.Count; i++)
            {
                Arr.Add(nodeSection.ChildNodes[i].Attributes["Value"].Value);
            }
            string ServerName = Arr[0].ToString();
            string UserName = Arr[1].ToString();
            string Password = Arr[2].ToString();
            string Catalog = Arr[3].ToString();
            Source = Source.Replace("@ServerName", ServerName);
            Source = Source.Replace("@UserName", UserName);
            Source = Source.Replace("@Password", Password);
            Source = Source.Replace("@Catalog", Catalog);
        }

        public static void Get_SourceForServer3()
        {
            Source = "Data Source=@ServerName; uid=@UserName; pwd=@Password; Initial Catalog=@Catalog;";
            string part = Application.StartupPath + "/ServerConfig.xml";
            XmlDocument Xml = new XmlDocument();
            Xml.Load(part);
            ArrayList Arr = new ArrayList();
            string selectNode = @"//Document/Data/server3/Items";
            XmlNode nodeSection = Xml.SelectSingleNode(selectNode);
            for (int i = 0; i < nodeSection.ChildNodes.Count; i++)
            {
                Arr.Add(nodeSection.ChildNodes[i].Attributes["Value"].Value);
            }
            string ServerName = Arr[0].ToString();
            string UserName = Arr[1].ToString();
            string Password = Arr[2].ToString();
            string Catalog = Arr[3].ToString();
            Source = Source.Replace("@ServerName", ServerName);
            Source = Source.Replace("@UserName", UserName);
            Source = Source.Replace("@Password", Password);
            Source = Source.Replace("@Catalog", Catalog);
        }

        public static void UpServerFailed()
        {
            string sqlInsert = @"";
            if (CheckNow == 1)
            {
            }
            else if (CheckNow == 2 || CheckNow == 3)
            {
                sqlInsert = @"INSERT INTO [dbo].[Warning_Log]
                           ([DateNow]
                           ,[WarningText]
                           ,[WarningName]
                           ,[Description]
                           ,[WarningGroup]
                           ,[Note]
                           ,[WarningNumber]
                           ,[WarningDate])
                     VALUES
                           (GETDATE()
                           ,'Server Cat Failed'
                           ,'High'
                           ,'SERVER FAIL'
                           ,'Monitor'
                           ,'Not Connect Server'
                           ,'1'
                           ,GETDATE())";
                Execute(sqlInsert, "");
            }
            else
            {
                sqlInsert = @"INSERT INTO [dbo].[Warning_Log]
                           ([DateNow]
                           ,[WarningText]
                           ,[WarningName]
                           ,[Description]
                           ,[WarningGroup]
                           ,[Note]
                           ,[WarningNumber]
                           ,[WarningDate])
                     VALUES
                           (GETDATE()
                           ,'All Server Failed'
                           ,'High'
                           ,'All SERVER FAIL'
                           ,'Monitor'
                           ,'Not Connect All Server'
                           ,'3'
                           ,GETDATE())";
                Execute(sqlInsert, "");
            }

        }
    }
}
